package com.glsc.ngateway.common.base.repo.mysql.opmanage.report.fileupload;


import com.alibaba.fastjson.JSONObject;
import com.glsc.ngateway.common.base.domain.mysql.opmanage.report.fileupload.TImportStatus;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.time.LocalDateTime;
import java.util.List;

@Repository
public interface TImportStatusRepo extends JpaRepository<TImportStatus, String>, JpaSpecificationExecutor<TImportStatus> {
    @Query(value = "select t1.fc_catid as catid,t1.fc_catname as catname,t2.fl_importid as importid,t3.fc_caption as caption,t3.fc_filename as filename,t4.fc_dimension1 as dimension1,t4.fl_status as status,t4.fc_msg as msg,t4.fd_begintime as begintime,t4.fd_endtime as endtime,t4.fc_modifier as modifier,\n" +
            "       (select count(1) \n" +
            "          from glsc_data.t_import_log t5 \n" +
            "         where t5.fc_catid = t4.fc_catid\n" +
            "           and t5.fc_bizdate = t4.fc_bizdate \n" +
            "           and t5.fl_importid = t4.fl_importid\n" +
            "           and t5.fl_status = 0\n" +
            "           and (t5.fc_dimension1 = :dimension1 or t5.fc_dimension1 is null)\n" +
            "       ) fl_count_imported,\n" +
            "       (select count(1) \n" +
            "          from glsc_data.file_upload_relate_import tt1, \n" +
            "                       glsc_data.file_upload_log tt2\n" +
            "                 where tt1.fc_catid  = t1.fc_catid \n" +
            "                   and tt1.fc_bizdate = :bizdate \n" +
            "                   and tt1.fl_is_del = 0\n" +
            "                   and tt2.id = tt1.fc_file_id \n" +
            "                   and tt2.filename REGEXP t3.fc_filename\n" +
            "                   and (tt1.fc_dimension1 = :dimension1 or tt1.fc_dimension1 is null)\n" +
            "       ) fl_count_matched,\n" +
            "       (select GROUP_CONCAT(tt2.filename)  \n" +
            "          from glsc_data.file_upload_relate_import tt1, \n" +
            "                       glsc_data.file_upload_log tt2\n" +
            "                 where tt1.fc_catid  = t1.fc_catid \n" +
            "                   and tt1.fc_bizdate = :bizdate \n" +
            "                   and tt1.fl_is_del = 0\n" +
            "                   and tt2.id = tt1.fc_file_id \n" +
            "                   and tt2.filename REGEXP t3.fc_filename\n" +
            "                   and (tt1.fc_dimension1 = :dimension1 or tt1.fc_dimension1 is null)\n" +
            "       ) fc_filelist_matched\n" +
            "  from glsc_data.t_import_cat t1\n" +
            "       inner join glsc_data.t_import_match t2 on t2.fc_catid = t1.fc_catid \n" +
            "       inner join glsc_data.t_import_config t3 on t3.fl_id = t2.fl_importid and t3.fl_is_enable = 1 \n" +
            "       left join glsc_data.t_import_status t4 \n" +
            "              on t4.fc_catid = t1.fc_catid\n" +
            "             and t4.fc_bizdate = :bizdate \n" +
            "             and t4.fl_importid = t3.fl_id \n" +
            "             and (t4.fc_dimension1 = :dimension1 or t4.fc_dimension1 is null)\n" +
            "  where t1.fc_catid = :catId \n" +
            "  order by t3.fl_id", nativeQuery = true)
    List<JSONObject> queryImportStatus(@Param("catId") String catId, @Param("bizdate") String bizdate, @Param("dimension1") String dimension1);

    @Query(value = "select max(t.id) from TImportStatus t")
    Long queryMaxId();

    @Transactional
    @Modifying
    @Query(value = "update TImportStatus t set t.msg = :msg where t.id = :id")
    void updateMsg(@Param("msg") String msg, @Param("id") Long id);

    @Transactional
    @Modifying
    @Query(value = "update TImportStatus t set t.endTime = :endtime where t.id = :id")
    void updateEndtime(@Param("endtime") LocalDateTime endtime, @Param("id") Long id);

    @Transactional
    @Modifying
    @Query(value = "update TImportStatus t set t.status = :status where t.id = :id")
    void updateStatus(@Param("status") Integer status, @Param("id") Long id);
}
